package com.thinkgem.jeesite.test.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;

public class ExcelUtilOne {

	//显示的导出表的标题
		private String title;
		
		//导出表的列名
		private String[] rowName ;
		
		//数据
		private List<Object[]>  dataList = new ArrayList<Object[]>();
		
		//其他参数
		private Map<String,Object> params = new HashMap<String, Object>();
		
		//构造方法，传入要导出的数据
		public ExcelUtilOne(String title,String[] rowName,List<Object[]>  dataList,Map<String,Object> params){
			this.dataList = dataList;
			this.rowName = rowName;
			this.title = title;
			this.params = params;
		}
				
		/**
		 * 导出数据
		 * @param response
		 * @throws Exception
		 */
		public void export(HttpServletResponse response) throws Exception{
			try{
				// 创建工作簿对象
				HSSFWorkbook workbook = new HSSFWorkbook();		
				// 创建工作表
				HSSFSheet sheet = workbook.createSheet(title);		 			
				
				// 产生表格标题行
		        HSSFRow rowm = sheet.createRow(0);
		        HSSFCell cellTiltle = rowm.createCell(0);
		        
		        //获取列头样式对象 
		        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
		        //单元格样式对象
		        HSSFCellStyle style = this.getStyle(workbook);		
		        
		        HSSFCell currentCell = null;
		        
		        //设置标题
		        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));  
		        cellTiltle.setCellStyle(columnTopStyle);
		        cellTiltle.setCellValue(title);
	    		
				// 定义所需列数
				int columnNum = rowName.length;
				// 在索引2的位置创建行(最顶端的行开始的第二行)
				HSSFRow rowRowName = sheet.createRow(2);				
				
				// 将列头设置到sheet的单元格中
				for(int n=0;n<columnNum;n++){
					//创建列头对应个数的单元格
					HSSFCell  cellRowName = rowRowName.createCell(n);
					//设置列头单元格的数据类型
					cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				
	    			HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
	    			//设置列头单元格的值
	    			cellRowName.setCellValue(text);
	    			//设置列头单元格样式
	    			cellRowName.setCellStyle(columnTopStyle);						
	    		}
				
	    		//将查询出的数据设置到sheet对应的单元格中
				for(int i=0;i<dataList.size();i++){
					
					//遍历每个对象
					Object[] obj = dataList.get(i);
					//创建所需的行数
					HSSFRow row = sheet.createRow(i+3);
					
					for(int j=0; j<obj.length; j++){
						//设置单元格的数据类型
						HSSFCell  cell = null;   
						if(j == 0){
							cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
							cell.setCellValue(i+1);	
						}else{
							if(!"".equals(obj[j]) && obj[j] != null){
								cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
								//设置单元格的值
								cell.setCellValue(obj[j].toString());
							}else {
								cell = row.createCell(j,HSSFCell.CELL_TYPE_BLANK);
								cell.setCellValue("");
							}
						}
						//设置单元格样式
						cell.setCellStyle(style);									
					}
				}

				//让列宽随着导出的列长自动适应
				for (int colNum = 0; colNum < columnNum; colNum++) {
		            int columnWidth = sheet.getColumnWidth(colNum) / 256;
		            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
		                HSSFRow currentRow;
		                //当前行未被使用过
		                if (sheet.getRow(rowNum) == null) {
		                    currentRow = sheet.createRow(rowNum);
		                } else {
		                    currentRow = sheet.getRow(rowNum);
		                }
		                if (null != currentRow.getCell(colNum)) {
		                    currentCell = currentRow.getCell(colNum);
		                    if(null != currentCell){
		                    	if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
		                    		if(null != currentCell.getStringCellValue()){
		                    			int length = currentCell.getStringCellValue().getBytes().length;
		                    			if (columnWidth < length) {
		                    				columnWidth = length;
		                    			}
		                    		}
		                    	}
		                    }
		                }
		            }
		            if(colNum == 0){
		            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
		            }else{
		            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
		            }
		        }
				
				//合并第一考核项，合并第一列和第七列，从第三行开始。
				mergeCell(sheet, 1, 3, sheet.getLastRowNum(), workbook,6);

				//合并第二考核项，合并第二列和第六列，从第三行开始。
				mergeCell(sheet, 2, 3, sheet.getLastRowNum(), workbook,4);
				
				//合计列
				HSSFRow rowRowN = sheet.createRow(sheet.getLastRowNum()+1);
				for(int i = 0 ; i < rowName.length; i ++ ){
					if(i == 0){
						HSSFCell cellRow= rowRowN.createCell(i);
						cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
						cellRow.setCellValue(dataList.size() + 1);
						cellRow.setCellStyle(style);
					}else if(i == 1){
						HSSFCell cellRow= rowRowN.createCell(i);
						cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
						cellRow.setCellValue("合计");
						cellRow.setCellStyle(columnTopStyle);
					}else if(i == rowName.length-1){
						HSSFCell cellRow= rowRowN.createCell(i);
						cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
						if(null != params.get("yearScore")){
							cellRow.setCellValue(params.get("yearScore").toString());
						}
						cellRow.setCellStyle(columnTopStyle);
					}else {
						HSSFCell cellRow= rowRowN.createCell(i);
						cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
						cellRow.setCellValue(" ");
						cellRow.setCellStyle(columnTopStyle);
					}
				}
				
				//导出excel，并下载
				if(workbook !=null){
					FileOutputStream fout = null;
					OutputStream out = null;
					String cityName = null;
					String year = null;
					try
					{
						if(null != params.get("cityName")){
							cityName = params.get("cityName").toString();
						}
						if(null != params.get("year")){
							year = params.get("year").toString();
						}
						
						String fileName = cityName + "市"+ year +"年成绩清单.xls";
//						String fileName = "地市成绩清单_" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
						String headStr = "attachment; filename=\"" +  java.net.URLEncoder.encode(fileName, "UTF-8") + "\"";
						response.setContentType("APPLICATION/OCTET-STREAM");
						response.setHeader("Content-Disposition", headStr);
						out = response.getOutputStream();
//						fout = new FileOutputStream("E:/cityScore.xls");
						workbook.write(out);
					} catch (IOException e){
						e.printStackTrace();
					} finally {
//						fout.close();
						out.close();
					}
				}

			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		
		/** 
		 * 列头单元格样式
		 */    
	  	public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
	  		
	  		  // 设置字体
	    	  HSSFFont font = workbook.createFont();
	    	  //设置字体大小
	    	  font.setFontHeightInPoints((short)11);
	    	  //字体加粗
	    	  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    	  //设置字体名字 
	    	  font.setFontName("Courier New");
	    	  //设置样式; 
	    	  HSSFCellStyle style = workbook.createCellStyle();
	    	  //设置底边框; 
	    	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    	  //设置底边框颜色;  
	    	  style.setBottomBorderColor(HSSFColor.BLACK.index);
	    	  //设置左边框;   
	    	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    	  //设置左边框颜色; 
	    	  style.setLeftBorderColor(HSSFColor.BLACK.index);
	    	  //设置右边框; 
	    	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    	  //设置右边框颜色; 
	    	  style.setRightBorderColor(HSSFColor.BLACK.index);
	    	  //设置顶边框; 
	    	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    	  //设置顶边框颜色;  
	    	  style.setTopBorderColor(HSSFColor.BLACK.index);
	    	  //在样式用应用设置的字体;  
	    	  style.setFont(font);
	    	  //设置自动换行; 
	    	  style.setWrapText(false);
	    	  //设置水平对齐的样式为居中对齐;  
	    	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	  //设置垂直对齐的样式为居中对齐; 
	    	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    	  
	    	  return style;
	    	  
	  	}
	  	
	  	/**  
		 * 列数据信息单元格样式
		 */  
	  	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		  	  // 设置字体
		  	  HSSFFont font = workbook.createFont();
		  	  //设置字体大小
		  	  //font.setFontHeightInPoints((short)10);
		  	  //字体加粗
		  	  //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		  	  //设置字体名字 
		  	  font.setFontName("Courier New");
		  	  //设置样式; 
		  	  HSSFCellStyle style = workbook.createCellStyle();
		  	  //设置底边框; 
		  	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		  	  //设置底边框颜色;  
		  	  style.setBottomBorderColor(HSSFColor.BLACK.index);
		  	  //设置左边框;   
		  	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		  	  //设置左边框颜色; 
		  	  style.setLeftBorderColor(HSSFColor.BLACK.index);
		  	  //设置右边框; 
		  	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		  	  //设置右边框颜色; 
		  	  style.setRightBorderColor(HSSFColor.BLACK.index);
		  	  //设置顶边框; 
		  	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		  	  //设置顶边框颜色;  
		  	  style.setTopBorderColor(HSSFColor.BLACK.index);
		  	  //在样式用应用设置的字体;  
		  	  style.setFont(font);
		  	  //设置自动换行; 
		  	  style.setWrapText(false);
		  	  //设置水平对齐的样式为居中对齐;  
		  	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		  	  //设置垂直对齐的样式为居中对齐; 
		  	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		  	 
		  	  return style;
	  	}
	  	
	  	/**  
	  	 * 合并单元格  
	  	 * @param sheet 要合并单元格的excel 的sheet
	  	 * @param cellLine  要合并的列  
	  	 * @param startRow  要合并列的开始行  
	  	 * @param endRow    要合并列的结束行  
	  	 * @param offset    与合并的列相同合并方式的列的偏移量
	  	 */  
	  	private static void mergeCell(HSSFSheet sheet, int cellLine, int startRow, int endRow,HSSFWorkbook workBook,int offset){   

	  	    // 样式对象    
	  		HSSFCellStyle style = workBook.createCellStyle(); 

	  		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	  		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  		
	  		//获取开始行的数据,以便后面进行比较   
	  		String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();   

	  		int count = 0;
	  		boolean flag = false;
	  		for (int i = startRow+1; i <= endRow; i++) {
	  			sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
	  			String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue(); 
	  			if(s_will.equals(s_current))
	  			{
	  				s_will = s_current;
	  				if(flag)
	  				{
	  					sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
	  					HSSFRow row = sheet.getRow(startRow-count);
	  					if(null != sheet.getRow(startRow-count).getCell(cellLine)){
	  						sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
	  						String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue(); 
	  						HSSFCell cell = row.createCell(cellLine);
	  						cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
	  						cell.setCellStyle(style); // 样式    
	  					}
	  					
	  					//合并与当前列相同合并方式的列
	  					sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine + offset,cellLine + offset));
	  					HSSFRow row1 = sheet.getRow(startRow-count);
	  					if(null != sheet.getRow(startRow-count).getCell(cellLine + offset)){
	  						sheet.getRow(i).getCell(cellLine + offset).setCellType(Cell.CELL_TYPE_STRING);
	  						String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue(); 
	  						HSSFCell cell = row1.createCell(cellLine + offset);
	  						cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
	  						cell.setCellStyle(style); // 样式    
	  					}
	  					count = 0;
	  					flag = false;
	  				}
	  				startRow=i;
	  				count++;   
	  			}else{
	  				flag = true;
	  				s_will = s_current;
	  			}
	  			
	  			if(i==endRow&&count>0)
	  			{
	  				/** 防止前面的漏合单元格，这里重新再合一次 start ***/
	  				sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
	  				HSSFRow row = sheet.getRow(startRow-count);
	  				if(null != sheet.getRow(startRow-count).getCell(cellLine)){
	  					sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
	  					String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue(); 
	  					HSSFCell cell = row.createCell(cellLine);
	  					cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
	  					cell.setCellStyle(style); // 样式    
	  				}

	  				sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine + offset,cellLine + offset));
	  				HSSFRow row1 = sheet.getRow(startRow-count);
	  				if(null != sheet.getRow(startRow-count).getCell(cellLine + offset)){
	  					sheet.getRow(i).getCell(cellLine + offset).setCellType(Cell.CELL_TYPE_STRING);
	  					String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue(); 
	  					HSSFCell cell = row1.createCell(cellLine + offset);
	  					cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
	  					cell.setCellStyle(style); // 样式    
	  				}
	  				/** 防止前面的漏合单元格，这里重新再合一次 end ***/

	  				//最后两条相同时，再合并
	  				if(sheet.getRow(i).getCell(cellLine).getStringCellValue().equals(sheet.getRow(i-1).getCell(cellLine).getStringCellValue())){
	  					sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine,cellLine));   
	  					String cellValueTemp2 = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue(); 
	  					HSSFRow row2 = sheet.getRow(startRow-count);
	  					HSSFCell cell2 = row2.createCell(cellLine);
	  					cell2.setCellValue(cellValueTemp2); // 跨单元格显示的数据    
	  					cell2.setCellStyle(style); // 样式

	  					//合并与当前列相同合并方式的列
	  					sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine + offset,cellLine + offset));   
	  					String cellValueTemp3 = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue(); 
	  					HSSFRow row3 = sheet.getRow(startRow-count);
	  					HSSFCell cell3 = row3.createCell(cellLine + offset);
	  					cell3.setCellValue(cellValueTemp3); // 跨单元格显示的数据    
	  					cell3.setCellStyle(style); // 样式    
	  				}
	  			}
	  		}
	  	}
	
}
